<?php
class Db_Message extends MVC_SQL {
    public function __construct() {
        $this->setTable('ams_message_board');
    }

    public function getMessageInbox($oModel) {
        $user_id=getUserIdLogin();
        $oModel->oNavigator = new paging();

        $page_size = $oModel->getData('page_size');
        $pagegroup_size = $oModel->getData('pagegroup_size');
        $stylesheet = $oModel->getData('stylesheet');

		/*sort*/
        $SortCol = ( $oModel->getData('SortCol') == "" ) ? "amb.message_date" : $oModel->getData( 'SortCol' ) ;
        $SortType = ( $oModel->getData('SortType') == "" ) ? "ASC" : $oModel->getData( 'SortType' ) ;
        $sOrderBy = " ORDER BY {$SortCol} {$SortType}" ;

        $sWhere=" WHERE 1 ";

        //FILTER DATE
        if ( ($oModel->getData('fromDate') != '') && ($oModel->getData('toDate') != '') ) {

            $fromdate = str_replace('/','-',$oModel->getData('fromDate') ) ;
            $todate = str_replace('/','-', $oModel->getData('toDate') ) ;
            $sWhere .= "AND date_format(amb.message_date,'%Y-%m-%d') >= '{$fromdate}'
					  AND date_format(amb.message_date,'%Y-%m-%d')<= '{$todate}'" ;
        }

        else  $sWhere .= "AND amb.message_date >= DATE_SUB(CURRENT_DATE(),INTERVAL 30 DAY)" ;



        $sSql="SELECT * FROM ams_message_board amb
		 	   INNER JOIN ams_admin aa ON
			   amb.message_from = aa.admin_id
            {$sWhere}
			   AND message_to = {$user_id} 
            {$sOrderBy}
            ";
        $total_record = $this->getNumRow($sSql);
        $oModel->oNavigator->init($sSql, $total_record, $page_size, $pagegroup_size, 'pUser', $stylesheet);
        $sSql = $oModel->oNavigator->getSql();



        return $this->getRecordsetExtend($sSql);
    }

    public function getMessageOutBox($oModel) {
        $user_id=getUserIdLogin();

        $oModel->oNavigator = new paging();
        $page_size = $oModel->getData('page_size');
        $pagegroup_size = $oModel->getData('pagegroup_size');
        $stylesheet = $oModel->getData('stylesheet');
			/*sort*/
        $SortCol = ( $oModel->getData('SortCol') == "" ) ? "amb.message_date" : $oModel->getData( 'SortCol' ) ;
        $SortType = ( $oModel->getData('SortType') == "" ) ? "ASC" : $oModel->getData( 'SortType' ) ;
        $sOrderBy = " ORDER BY {$SortCol} {$SortType}" ;

        $sWhere=" WHERE 1 ";

        //FILTER DATE
        if ( ($oModel->getData('fromDate') != '') && ($oModel->getData('toDate') != '') ) {


            $fromdate = str_replace('/','-',$oModel->getData('fromDate') ) ;
            $todate = str_replace('/','-', $oModel->getData('toDate') ) ;
            $sWhere .= "AND date_format(amb.message_date,'%Y-%m-%d') >= '{$fromdate}'
					  AND date_format(amb.message_date,'%Y-%m-%d')<= '{$todate}'" ;
        }

        else  $sWhere .= "AND amb.message_date >= DATE_SUB(CURRENT_DATE(),INTERVAL 30 DAY)" ;

        $sSql=" SELECT amb.*,aa.admin_username
				FROM ams_message_board amb INNER JOIN ams_admin aa ON
				amb.message_to = aa.admin_id		
            {$sWhere} AND message_from = {$user_id}
            {$sOrderBy}
            ";

        $total_record = $this->getNumRow($sSql);
        $oModel->oNavigator->init($sSql, $total_record, $page_size, $pagegroup_size, 'pUser', $stylesheet);
        $sSql = $oModel->oNavigator->getSql();
        return $this->getRecordsetExtend($sSql);
    }

    public function insert_new_message($oModel) {

    }

    public function updateMessage($aData) {
        return $this->doUpdateByPK($aData);
    }

    public function getListUser($oModel) {
        $sSql="SELECT * FROM ams_admin WHERE admin_status=1";
        return $this->getRecordsetExtend($sSql);
    }

    public function getInfoOutBoxMessage($id) {
        $sSql="SELECT amb.*,aa.admin_username
				FROM ams_message_board amb INNER JOIN ams_admin aa ON
				(amb.message_from = aa.admin_id)
				WHERE message_id = {$id}";
        return $this->getRecordsetByPKExtend($sSql);
    }

    public function getInfoUserById($id) {
        $sSql="SELECT admin_username as user_to FROM ams_admin WHERE admin_id ={$id} ";
        return $this->getRecordsetByPKExtend($sSql);
    }

    public function updateMessageStatus($id) {
        $sSql="UPDATE ams_message_board set message_status =1 WHERE message_id={$id}";

        return $this->doUpdateExtend($sSql);
    }

    /**
     * MESSAGE BOARD
     */
    /**
     * MESSAGE BOARD
     */
    public function getListMessageBoard($oModel) {

        $today=date('Y-m-d');
        $oModel->oNavigator = new paging();
        $page_size = $oModel->getData('page_size');
        $pagegroup_size = $oModel->getData('pagegroup_size');
        $stylesheet = $oModel->getData('stylesheet');
			/*sort*/
        $SortCol = ( $oModel->getData('SortCol') == "" ) ? "amb.message_date" : $oModel->getData( 'SortCol' ) ;
        $SortType = ( $oModel->getData('SortType') == "" ) ? "ASC" : $oModel->getData( 'SortType' ) ;
        $sOrderBy = " ORDER BY {$SortCol} {$SortType}" ;

        $sWhere=" WHERE 1 ";

        //FILTER DATE
        if ( ($oModel->getData('fromDate') != '') && ($oModel->getData('toDate') != '') ) {


            $fromdate = str_replace('/','-',$oModel->getData('fromDate') ) ;
            $todate = str_replace('/','-', $oModel->getData('toDate') ) ;
            $sWhere .= "AND date_format(amb.message_date,'%Y-%m-%d') >= '{$fromdate}'
					  AND date_format(amb.message_date,'%Y-%m-%d')<= '{$todate}'" ;
        }

        else  $sWhere .= "AND date_format(amb.message_date,'%Y-%m-%d') = '{$today}'" ;



        $sSql="SELECT * FROM ams_message_board amb
		 	   INNER JOIN ams_admin aa ON
			   amb.message_from = aa.admin_id
            {$sWhere}
            {$sOrderBy}";

        $total_record = $this->getNumRow($sSql);
        $oModel->oNavigator->init($sSql, $total_record, $page_size, $pagegroup_size, 'pUser', $stylesheet);
        $sSql = $oModel->oNavigator->getSql();

        return $this->getRecordsetExtend($sSql);
    }
}
?>